package com.etbsms.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Conjunction;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.type.Type;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import com.etbsms.dao.IAgricultureAndFoodDAO;
import com.etbsms.domain.AgricultureAndFood;
import com.etbsms.domain.CodeCountry;
import com.etbsms.domain.CodePlant;
import com.etbsms.domain.Files;
import com.etbsms.domain.Users;
import com.etbsms.form.PageForm;
import com.etbsms.form.QueryAAFForm;
import com.etbsms.util.Constants;
import com.etbsms.util.Constants.FLOW_TYPE;

@Repository
@Component("agricultureAndFoodDAO")
public class AgricultureAndFoodDAOImpl extends GenericDAOImpl<AgricultureAndFood, Integer> 
	implements IAgricultureAndFoodDAO{

	@Override
	@SuppressWarnings("unchecked")
	public PageForm<AgricultureAndFood> getOwnForms(Users users,PageForm<AgricultureAndFood> form,boolean isAnalyzed) {		
		Session session = sessionFactory.openSession();
		Criteria criteria = session.createCriteria(AgricultureAndFood.class);		
		
		criteria.add(Restrictions.eq("users", users));
		
		if(isAnalyzed){
			criteria.add(Restrictions.eq("analyzed", true));
//			
//			Disjunction disj = Restrictions.disjunction();	
//			
//			disj.add(Restrictions.eq("flow", Constants.FLOW_KENI));
			Criterion c1 = Restrictions.eq("flow", Constants.FLOW_KENI);
			Conjunction conj = Restrictions.conjunction();
			conj.add(Restrictions.eq("flow", Constants.FLOW_RESA));
			conj.add(Restrictions.eq("temporary", false));
			criteria.add(Restrictions.or(c1,conj));			
		}
		
		
		if(form.getQueryType() != null && form.getQueryType().trim().length() != 0){			
			if(form.getQueryType().indexOf("9") == -1){
				Disjunction _disj = Restrictions.disjunction();				
				for(String qt : form.getQueryType().split(",")){
					int _qr = Integer.parseInt(qt.trim());
					_disj.add(Restrictions.eq("flow", _qr));					
				}
				criteria.add(_disj);
			}
		}
		
		criteria.addOrder(Order.asc("rec_no"));		
		if(form.isRequery())
			form.setTotalRows(criteria.list().size());
		criteria.setFirstResult(form.getFirstResult());
		criteria.setMaxResults(form.getRowsPerPage());		
		List<AgricultureAndFood> list = criteria.list();
		form.setObj(list);
		session.close();
		return form;
	}
	
	/*
	@Override
	@SuppressWarnings("unchecked")
	public PageForm<AgricultureAndFood> getOwnChecker(Users users,PageForm<AgricultureAndFood> form) {		
		Session session = sessionFactory.openSession();
		Criteria criteria = session.createCriteria(AgricultureAndFood.class);
		criteria.add(Restrictions.eq("checker_user", users));
		criteria.add(Restrictions.eq("enabled", true));
		criteria.addOrder(Order.asc("rec_no"));		
		if(form.isRequery())
			form.setTotalRows(criteria.list().size());
		criteria.setFirstResult(form.getFirstResult());
		criteria.setMaxResults(form.getRowsPerPage());		
		List<AgricultureAndFood> list = criteria.list();
		form.setObj(list);
		session.close();
		return form;
	}
	*/
	
	@Override
	public AgricultureAndFood get(Integer seq_no) {		
		Session session = sessionFactory.openSession();		
		AgricultureAndFood entity = (AgricultureAndFood)session.get(AgricultureAndFood.class, seq_no);
		for(CodePlant plant : entity.getPlants()){
			//System.out.println(plant.getCode());
		}
		for(CodeCountry country : entity.getCountries()){
			//System.out.println(country.getCode());
		}
		for(Files files : entity.getFiles()){
			//System.out.print(files.getSeq_no());
		}
		session.close();
		return entity;
	}

	@Override
	@SuppressWarnings("unchecked")
	public List<AgricultureAndFood> getAll(int flow,FLOW_TYPE type) {
		Session session = sessionFactory.openSession();	
		Criteria criteria = session.createCriteria(AgricultureAndFood.class);
		criteria.add(Restrictions.isNotNull("title_o"));
		criteria.add(Restrictions.isNotNull("author"));
		
		if(type == Constants.FLOW_TYPE.ge){
			criteria.add(Restrictions.ge("flow",flow));
		}else if(type == Constants.FLOW_TYPE.eq){
			criteria.add(Restrictions.eq("flow",flow));
		}
			
		List<AgricultureAndFood> list = criteria.list();
		session.close();
		return list;
	}

	@Override
	public AgricultureAndFood get(String rec_no) {
		Session session = sessionFactory.openSession();	
		Criteria criteria = session.createCriteria(AgricultureAndFood.class);
		criteria.add(Restrictions.eq("rec_no",rec_no));
		AgricultureAndFood entity = (AgricultureAndFood)criteria.list().get(0);

		for(CodePlant plant : entity.getPlants()){
			//System.out.println(plant.getCode());
		}
	
		for(CodeCountry country : entity.getCountries()){
			//System.out.println(country.getCode());
		}
		
		for(Files files : entity.getFiles()){
			//System.out.println(files.getSeq_no());
		}

		session.close();		
		return entity;
	}

	
	//@Override
	public QueryAAFForm query_backup(QueryAAFForm form,FLOW_TYPE type) {
//		Session session = sessionFactory.openSession();	
//		Criteria criteria = session.createCriteria(AgricultureAndFood.class);
//		Disjunction disj = Restrictions.disjunction();		//or
//		//Conjunction conj = Restrictions.conjunction();		//and
//		
//		criteria.add(Restrictions.eq("enabled",true));
//		
//		if(type == FLOW_TYPE.eq){
//			criteria.add(Restrictions.eq("flow",form.getFlow()));
//		}else if(type == FLOW_TYPE.ge){
//			criteria.add(Restrictions.ge("flow",form.getFlow()));
//		}
//		
//		
//		/** 年份查詢*********************************************/
//		int sYear = form.getStart_year();
//		int eYear = form.getEnd_year();			
//		switch(form.getYear_type()){
//		case 1:
//			criteria.add(Restrictions.ge("year", sYear));
//			break;
//		case 2:
//			criteria.add(Restrictions.le("year", sYear));
//			break;
//		case 3:
//			criteria.add(Restrictions.eq("year", sYear));
//			break;
//		case 4:
//			criteria.add(Restrictions.ge("year", sYear));
//			criteria.add(Restrictions.le("year", eYear));
//			break;
//		}
//		
//		String title_o = form.getTitle_o();
//		String author = form.getAuthor();
//		String abstract_o = form.getAbstract_o();
//		String keyword_o = form.getKeyword_o();
//		String[] langs = form.getLanguage();
//		String[] doc_type = form.getDoc_type();
//		String[] clazz = form.getClazz();
//		
//		if(form.isQuery_type()){
//			if(title_o != null && title_o.trim().length() != 0)
//				criteria.add(Restrictions.ilike("title_o", form.getTitle_o()));
//			
//			if(author != null && author.trim().length() != 0)
//				criteria.add(Restrictions.ilike("author", form.getAuthor()));
//			
//			if(abstract_o != null && abstract_o.trim().length() != 0)
//				criteria.add(Restrictions.ilike("abstract_o", form.getAbstract_o()));
//			
//			if(keyword_o != null && keyword_o.trim().length() != 0)
//				criteria.add(Restrictions.ilike("keyword_o", form.getKeyword_o()));
//		}else{			
//			if(title_o != null && title_o.trim().length() != 0)
//				disj.add(Restrictions.ilike("title_o", form.getTitle_o()));
//			
//			if(author != null && author.trim().length() != 0)
//				disj.add(Restrictions.ilike("author", form.getAuthor()));
//			
//			if(abstract_o != null && abstract_o.trim().length() != 0)
//				disj.add(Restrictions.ilike("abstract_o", form.getAbstract_o()));
//			
//			if(keyword_o != null && keyword_o.trim().length() != 0)
//				disj.add(Restrictions.ilike("keyword_o", form.getKeyword_o()));			
//		}
//		
//		/**語言查詢***********************************************/
//		Disjunction disj1 = Restrictions.disjunction();
//		criteria.createAlias("language", "language");		
//		if(langs != null && langs.length > 0)
//			for(String _lang : langs)
//				disj1.add(Restrictions.eq("language.code", _lang));
//		criteria.add(disj1);
//		
//		/**資料類別查詢*******************************************/
//		Disjunction disj2 = Restrictions.disjunction();
//		if(doc_type != null && doc_type.length > 0)
//			for(String _doc_type : doc_type)
//				disj2.add(Restrictions.eq("doc_type", _doc_type));
//		criteria.add(disj2);			
//		/**主題分類查詢*******************************************/
//		Disjunction disj3 = Restrictions.disjunction();
//		if(clazz != null && clazz.length > 0)
//			for(String _clazz : clazz)
//				disj3.add(Restrictions.ilike("clazz", "%"+_clazz+"%"));
//		criteria.add(disj3);			
//		/**植物查詢***********************************************/
//		if(form.getPlants_code() != null && form.getPlants_code().length() > 0){
//			Disjunction disj4 = Restrictions.disjunction();
//			criteria.createAlias("plants", "plants");		
//			disj4.add(Restrictions.in("plants.code", form.getPlants_code().split(",")));
//			criteria.add(disj4);
//		}		
//		/**國家查詢***********************************************/
//		if(form.getCountries_code() != null && form.getCountries_code().length() > 0){
//			Disjunction disj5 = Restrictions.disjunction();
//			criteria.createAlias("countries", "countries");	
//			disj5.add(Restrictions.in("countries.code", form.getCountries_code().split(",")));		
//			criteria.add(disj5);
//		}
//		/********************************************************/
//		criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);		//distinct		
//		criteria.add(disj);		
//		criteria.addOrder(Order.asc("rec_no"));		
//		if(form.isRequery() || true){
//			form.setTotalRows(criteria.list().size());	
//		}
//		//System.out.println(form.getFirstResult() + " - " + form.getRowsPerPage() + " - " + form.getTotalRows() + " - " + form.getFlow());
//		
//		System.out.println("FirstResult 	: " + form.getFirstResult());
//		System.out.println("TotalRows   	: " + form.getTotalRows());
//		System.out.println("1.NowPage		: " + form.getNowPage());
//		
//		//(this.getNowPage() - 1) * this.getRowsPerPage()
//		
//		while(((form.getNowPage() - 1) * form.getRowsPerPage()) >= form.getTotalRows()){
//			form.setNowPage(form.getNowPage() - 1);
//		}
//		System.out.println("2.NowPage		: " + form.getNowPage());
//		
//		criteria.setFirstResult(form.getFirstResult());		
//		criteria.setMaxResults(form.getRowsPerPage());			
//		List<AgricultureAndFood> list = criteria.list();
//		form.setObj(list);
//		return form;
		return null;
	}
	
	@Override
	@SuppressWarnings("unchecked")
	public QueryAAFForm query(QueryAAFForm form,FLOW_TYPE type) {
		Session session = sessionFactory.openSession();	
		Criteria criteria = session.createCriteria(AgricultureAndFood.class);
		Disjunction disj = Restrictions.disjunction();		//or
		//Conjunction conj = Restrictions.conjunction();		//and
		
		if(type == FLOW_TYPE.eq){
			criteria.add(Restrictions.eq("flow",form.getFlow()));
		}else if(type == FLOW_TYPE.ge){
			criteria.add(Restrictions.ge("flow",form.getFlow()));
		}
		
		
		/** 建立者*********************************************/
		if(form.getCreated_users() != 0){
			criteria.createAlias("users", "users");
			criteria.add(Restrictions.eq("users.seq_no", form.getCreated_users()));			
		}
		
		
		/** 年份查詢*********************************************/
		int sYear = form.getStart_year();
		int eYear = form.getEnd_year();			
		switch(form.getYear_type()){
		case 1:
			criteria.add(Restrictions.ge("year", sYear));
			break;
		case 2:
			criteria.add(Restrictions.le("year", sYear));
			break;
		case 3:
			criteria.add(Restrictions.eq("year", sYear));
			break;
		case 4:
			criteria.add(Restrictions.ge("year", sYear));
			criteria.add(Restrictions.le("year", eYear));
			break;
		}
		
		String[] langs = form.getLanguage();
		String[] doc_type = form.getDoc_type();
		String[] clazz = form.getClazz();
		Integer[] literature = form.getLiterature();
		
		
		/****************************************************************************************************************************/		
		String basic_sql = "(lower(title_o) like ? or lower(author) like ? or lower(abstract_o) like ? or lower(keyword_o) like ?) ";
		String sql = "";
		String[] qrBoolean = form.getQr_boolean().split(",");
		List<String> qrValue = new ArrayList<String>();
		
		for(int i=0;i<form.getQr().length;i++){			
				for(int j=0;j<4;j++)
					qrValue.add(("%" + form.getQr()[i].trim() + "%"));				
				sql += (i <= qrBoolean.length - 1) ? basic_sql + qrBoolean[i] : basic_sql;
		}
		
		String[] _qrValue = new String[qrValue.size()];
		Type[] _qrType = new Type[qrValue.size()];
		
		for(int i=0;i<qrValue.size();i++){
			_qrValue[i] = qrValue.get(i);
			_qrType[i] = Hibernate.STRING;
		}
			
		criteria.add(Restrictions.sqlRestriction(
			sql, _qrValue,_qrType
		));
		/****************************************************************************************************************************/
		
		
		/**語言查詢***********************************************/
		Disjunction disj1 = Restrictions.disjunction();
		criteria.createAlias("language", "language");		
		if(langs != null && langs.length > 0)
			for(String _lang : langs)
				disj1.add(Restrictions.eq("language.code", _lang));
		criteria.add(disj1);
		/**文獻性質查詢*******************************************/
		if(literature.length != 0 && literature.length != 4){
			Disjunction disj6 = Restrictions.disjunction();
			if(literature != null && literature.length > 0)
				for(int _literature : literature)
					disj6.add(Restrictions.eq("literature", _literature));
			criteria.add(disj6);
		}	
		
		/**資料類別查詢*******************************************/
		Disjunction disj2 = Restrictions.disjunction();
		if(doc_type != null && doc_type.length > 0)
			for(String _doc_type : doc_type)
				disj2.add(Restrictions.eq("doc_type", _doc_type));
		criteria.add(disj2);			
		/**主題分類查詢*******************************************/
		Disjunction disj3 = Restrictions.disjunction();
		if(clazz != null && clazz.length > 0)
			for(String _clazz : clazz)
				disj3.add(Restrictions.ilike("clazz", "%"+_clazz+"%"));
		criteria.add(disj3);	
			
		/**植物查詢***********************************************/
		if(form.getPlants_code() != null && form.getPlants_code().length() > 0){
			Disjunction disj4 = Restrictions.disjunction();
			criteria.createAlias("plants", "plants");		
			disj4.add(Restrictions.in("plants.code", form.getPlants_code().split(",")));
			criteria.add(disj4);
		}		
		/**國家查詢***********************************************/
		if(form.getCountries_code() != null && form.getCountries_code().length() > 0){
			Disjunction disj5 = Restrictions.disjunction();
			criteria.createAlias("countries", "countries");	
			disj5.add(Restrictions.in("countries.code", form.getCountries_code().split(",")));		
			criteria.add(disj5);
		}
		/********************************************************/
		criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);		//distinct		
		criteria.add(disj);		
		criteria.addOrder(Order.asc("rec_no"));		
		if(form.isRequery()){
			form.setTotalRows(criteria.list().size());	
		}
		
		while(((form.getNowPage() - 1) * form.getRowsPerPage()) >= form.getTotalRows()){
			form.setNowPage(form.getNowPage() - 1);
		}		
		criteria.setFirstResult(form.getFirstResult());		
		criteria.setMaxResults(form.getRowsPerPage());			
		List<AgricultureAndFood> list = criteria.list();
		form.setObj(list);
		session.close();
		return form;
	}

	@Override
	@SuppressWarnings("unchecked")
	public QueryAAFForm recNo_query(QueryAAFForm form, FLOW_TYPE type) {
		Session session = sessionFactory.openSession();	
		Criteria criteria = session.createCriteria(AgricultureAndFood.class);	
		criteria.add(Restrictions.eq("flow",form.getFlow()));		
		criteria.add(Restrictions.in("rec_no",	recNumber(form.getRec_no())));
		
		
		if(form.isRequery()){
			form.setTotalRows(criteria.list().size());	
		}
		

		criteria.setFirstResult(form.getFirstResult());		
		criteria.setMaxResults(form.getRowsPerPage());			
		List<AgricultureAndFood> list = criteria.list();
		form.setObj(list);
		session.close();
		return form;
	}
	
	private static String recNumber(int n){
		String _n = String.valueOf(n);
		for(int i=_n.length();i<6;i++)
			_n = "0" + _n;		
		return _n;		
	}
	
	private static List<String> recNumber(String rec){
		List<String> list = new ArrayList<String>();		
		for(String _n : rec.split(",")){
			if(!_n.matches("[0-9-\\s]*"))
				continue;
			_n = _n.trim();				
			if(_n.indexOf("-") != -1){			
				int start 	= Integer.parseInt((_n.split("-")[0]).trim());
				int end			= Integer.parseInt((_n.split("-")[1]).trim());
				
				for(int i=start;i<=end;i++)
					list.add(recNumber(i));				
			}else {
				list.add(recNumber(Integer.parseInt(_n)));
			}			
		}
		return list;		
	}

	@Override	
	public List<Users> getInputUsers() {
		Session session = sessionFactory.openSession();	
		Criteria criteria = session.createCriteria(AgricultureAndFood.class);
		criteria.setProjection(Projections.groupProperty("users"));
		criteria.addOrder(Order.asc("users"));		
		List<Users> users = criteria.list();
		session.close();
		return users;
	}

	@Override
	public void changeTemporary(Users users) {
		Session session = sessionFactory.openSession();
		Query query = session.createQuery("update AgricultureAndFood set temporary = true where flow = ? and users = ?");
		query.setInteger(0, Constants.FLOW_RESA);
		query.setEntity(1, users);
		query.executeUpdate();
		session.close();			
	}
}
